package main

import (
	"fmt"
	"log"
	"strings"

	"github.com/xwb1989/sqlparser"
)

func main() {
	// 包含多条 SQL 语句的 DDL 字符串，其中注释里含有分号
	sql := `CREATE TABLE lc.t_lk_trans_bill_detail (
	IS_DELETED VARCHAR(1) COMMENT '是否删除：1-删除; 0-未删除',
	REMARK VARCHAR(1000) COMMENT '备注',
	EXT_COL2 VARCHAR(500) COMMENT '扩展字段2',
	EXT_COL3 VARCHAR(500) COMMENT '扩展字段3',
	EXT_COL4 VARCHAR(500) COMMENT '扩展字段4',
	EXT_COL5 VARCHAR(500) COMMENT '扩展字段5',
    lk_trans_bill_detail_id VARCHAR(64) PRIMARY KEY COMMENT '运单明细信息 ID',
    goods_trans_unit_price DECIMAL(25,14) COMMENT '单价',
    goods_trans_totle_amt DECIMAL(30,14) COMMENT '总额'
) COMMENT '运单明细信息表';

`

	// 将整个字符串分割为单独的SQL语句
	statements, err := sqlparser.SplitStatementToPieces(sql)
	if err != nil {
		log.Fatalf("Error splitting SQL statements: %v", err)
	}

	fmt.Printf("Found %d SQL statements:\n\n", len(statements))

	for i, stmtStr := range statements {
		if strings.TrimSpace(stmtStr) == "" {
			continue // 跳过空行或空白语句
		}

		fmt.Printf("=== Statement %d ===\n", i+1)
		fmt.Printf("Original SQL: %s\n", stmtStr)

		// 解析单个 SQL 语句
		stmt, err := sqlparser.Parse(stmtStr)
		if err != nil {
			log.Printf("Error parsing SQL: %v", err)
			continue
		}

		// 根据语句类型进行处理
		switch ddl := stmt.(type) {
		case *sqlparser.DDL:
			fmt.Printf("DDL Action: %s\n", ddl.Action)
			fmt.Printf("Table: %s\n", ddl.Table.Name.String())

			switch ddl.Action {
			case "create":
				if ddl.TableSpec != nil {
					fmt.Printf("Table columns: %d\n", len(ddl.TableSpec.Columns))
					for j, col := range ddl.TableSpec.Columns {
						fmt.Printf("  Column %d: %s %s\n", j+1, col.Name.String(), col.Type.SQLType())
					}
				}
			case "alter":
				fmt.Printf("ALTER TABLE operation\n")
			}
		default:
			fmt.Printf("Statement type: %T\n", stmt)
		}
		fmt.Println()
	}

	// 演示更多功能
	demonstrateAdvancedFeatures()
}

func demonstrateAdvancedFeatures() {
	fmt.Println("\n=== Advanced Features Demo ===")

	// 示例1：提取注释
	sqlWithComment := "CREATE TABLE users (id INT, name VARCHAR(100)) /* This is a table comment */;"
	strippedSQL := sqlparser.StripLeadingComments(sqlWithComment)
	fmt.Printf("Original SQL: %s\n", sqlWithComment)
	fmt.Printf("Stripped SQL: %s\n", strippedSQL)
	fmt.Println()

	// 示例2：分析复杂DDL
	complexDDL := `
	CREATE TABLE orders (
		id BIGINT PRIMARY KEY,
		user_id INT NOT NULL,
		product_name VARCHAR(255),
		quantity INT DEFAULT 1,
		price DECIMAL(10,2),
		created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
		INDEX idx_user_id (user_id),
		INDEX idx_product_name (product_name)
	);`

	stmt, err := sqlparser.Parse(complexDDL)
	if err != nil {
		log.Printf("Error parsing complex DDL: %v", err)
		return
	}

	if ddl, ok := stmt.(*sqlparser.DDL); ok {
		fmt.Printf("=== Complex Table Analysis ===\n")
		fmt.Printf("Table: %s\n", ddl.Table.Name.String())
		fmt.Printf("Action: %s\n", ddl.Action)

		if ddl.TableSpec != nil {
			fmt.Printf("Columns:\n")
			for i, col := range ddl.TableSpec.Columns {
				fmt.Printf("  %d. %s %s", i+1, col.Name.String(), col.Type.SQLType())
				if col.Type.Default != nil {
					fmt.Printf(" DEFAULT %s", sqlparser.String(col.Type.Default))
				}
				fmt.Println()
			}

			fmt.Printf("Indexes:\n")
			for i, idx := range ddl.TableSpec.Indexes {
				fmt.Printf("  %d. %s on columns: ", i+1, idx.Info.Name.String())
				for j, col := range idx.Columns {
					if j > 0 {
						fmt.Printf(", ")
					}
					fmt.Printf("%s", col.Column.String())
				}
				fmt.Println()
			}
		}
	}

	// 示例3：使用String方法格式化AST
	fmt.Println("\n=== AST String Representation ===")
	if ddl, ok := stmt.(*sqlparser.DDL); ok {
		fmt.Printf("DDL AST String: %s\n", sqlparser.String(ddl))
	}

	// 示例4：演示SQL分析和重构
	demonstrateSQLAnalysis()
}

func demonstrateSQLAnalysis() {
	fmt.Println("\n=== SQL Analysis and Manipulation Demo ===")

	// 演示1：识别语句类型
	testSQLs := []string{
		"SELECT * FROM users WHERE id = 1",
		"INSERT INTO users (name, email) VALUES ('John', 'john@example.com')",
		"UPDATE users SET name = 'Jane' WHERE id = 1",
		"DELETE FROM users WHERE id = 1",
		"CREATE TABLE temp (id INT PRIMARY KEY)",
		"DROP TABLE IF EXISTS temp",
	}

	fmt.Println("=== Statement Type Detection ===")
	for i, sql := range testSQLs {
		fmt.Printf("%d. SQL: %s\n", i+1, sql)
		if sqlparser.IsDML(sql) {
			fmt.Printf("   Type: DML (Data Manipulation Language)\n")
		} else {
			fmt.Printf("   Type: Non-DML (likely DDL or DQL)\n")
		}
	}
	fmt.Println()

	// 演示2：提取变量绑定
	insertSQL := "INSERT INTO users (name, age) VALUES (:name, :age)"
	stmt, err := sqlparser.Parse(insertSQL)
	if err != nil {
		fmt.Printf("Error parsing insert SQL: %v\n", err)
		return
	}

	fmt.Printf("=== Bind Variable Analysis ===\n")
	fmt.Printf("SQL: %s\n", insertSQL)
	bindVars := sqlparser.GetBindvars(stmt)
	if len(bindVars) > 0 {
		fmt.Printf("Bind variables found: %v\n", bindVars)
	} else {
		fmt.Printf("No bind variables found\n")
	}
	fmt.Println()

	// 演示3：SQL注释处理
	commentedSQL := `-- This is a leading comment
SELECT * FROM users /* inline comment */
WHERE name = 'John' -- trailing comment`

	fmt.Printf("=== Comment Processing ===\n")
	fmt.Printf("Original SQL:\n%s\n", commentedSQL)
	stripped := sqlparser.StripLeadingComments(commentedSQL)
	fmt.Printf("After stripping leading comments:\n%s\n", stripped)

	// 演示4：SQL语句分割
	multiStatementSQL := "CREATE TABLE t1 (id INT); INSERT INTO t1 VALUES (1); SELECT * FROM t1;"
	fmt.Printf("\n=== Multi-statement SQL Processing ===\n")
	fmt.Printf("Original SQL: %s\n", multiStatementSQL)

	pieces, err := sqlparser.SplitStatementToPieces(multiStatementSQL)
	if err != nil {
		fmt.Printf("Error splitting statements: %v\n", err)
		return
	}

	fmt.Printf("Split into %d statements:\n", len(pieces))
	for i, piece := range pieces {
		if strings.TrimSpace(piece) != "" {
			fmt.Printf("  %d. %s\n", i+1, strings.TrimSpace(piece))
		}
	}
}
